Designing Correlation Indices with Bucketing and Composition

In relational query processing, there are generally two choices for access paths when performing a predicate lookup for which no clustered index is available. One option is to use an unclustered index. Another is to perform a complete sequential scan of the table. Online analytical processing (OLAP) workloads often do not benefit from the availability of unclustered indices; the cost of random disk I/O becomes prohibitive for all but the most selective queries. Unfortunately, this means that data warehouses and other OLAP systems will frequently perform sequential scans, unless they can satisfy nearly all of the queries posed to them by a single clustered index [6], or unless specialized data structures – like bitmap indices, materialized views, or cubes – can be used to answer queries directly. We present a new index data structure called a correlation index (CI) that enables OLAP databases to answer a wider range of queries from a single clustered index or sorted file. The CI exploits correlations between the key attribute of a clustered index and other unclustered attributes in the table. We show that CIs can be implemented as an “add-on” to an existing database via a simple query rewriting scheme. In order to predict when CIs will exhibit wins over alternative access methods, we develop an analytical cost model that is suitable for integration with existing query optimizers. We also develop algorithms that search for strong candidate CIs and that recommend ways to “bucket” CIs to reduce their space utilization. We compare CI performance against sequential scans and unclustered B+Tree indices in PostgreSQL. Our results on several different data sets validate the accuracy of our cost model and establish numerous cases where CIs accelerate lookup times dramatically over other access methods. We also show that standard B+Trees can benefit from correlations just as CIs do, and that CIs are typically much smaller than B+Trees (by up to three orders of magnitude), making it possible to maintain many of them in memory over a single table.